package com.wuda.dao;

import com.wuda.bean.Book;
import com.wuda.bean.BorrowRecord;
import com.wuda.bean.User;
import com.wuda.utils.Base;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BorrowRecordDao {


	/**
	 *  根据用户id 进行借书操作
	 *
	 * @param borrowRecord 借书参数
	 * @return 200:  "借书成功"
	 * 201:  "借书失败，书不存在"
	 * 202: "借书失败，该图书库存不足"
	 * 203:  "借书失败，用户已经借了这本书"
	 */
	public int addBorrowRecord(BorrowRecord borrowRecord) {

		Connection connection = null;
		try {
			connection = Base.getConnection();

			connection.setAutoCommit(false);

			// 判断图书库存是否足够
			String checkStockSql = "SELECT stock_quantity FROM book WHERE book_id = ?";
			ResultSet resultSet = Base.executeQuery(connection, checkStockSql, new Object[]{borrowRecord.getBookId()});
			if (resultSet.next()) {
				int stockQuantity = resultSet.getInt("stock_quantity");
				if (stockQuantity <= 0) {
					return 202; // 图书库存不足
				}
			} else {
				return 201; // 书不存在
			}


			// 判断用户是否已经借了这本书
			String checkBorrowSql = "SELECT * FROM borrow_record WHERE user_id = ? AND book_id = ? AND status = 'borrowed'";
			resultSet = Base.executeQuery(connection, checkBorrowSql, new Object[]{borrowRecord.getUserId(), borrowRecord.getBookId()});
			if (resultSet.next()) {
				return 203; // 用户已经借了这本书
			}

			// 插入新的借阅记录
			String insertRecordSql = "INSERT INTO borrow_record (user_id, book_id, borrow_date) VALUES (?, ?, ?)";
			Object[] params = {
					borrowRecord.getUserId(),
					borrowRecord.getBookId(),
					new java.sql.Date(borrowRecord.getBorrowDate().getTime()),
			};

			Base.executeUpdate(connection, insertRecordSql, params);

			// 更新图书库存
			String updateBookSql = "UPDATE book SET stock_quantity = stock_quantity - 1 WHERE book_id = ?";

			Base.executeUpdate(connection, updateBookSql, new Object[]{borrowRecord.getBookId()});

			connection.commit();
			return 200; // 借书成功

		} catch (SQLException | ClassNotFoundException e) {
			if (connection != null) {
				try {
					connection.rollback();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
			e.printStackTrace();
			return 500; // 系统错误
		} finally {
			try {
				if (connection != null) {
					connection.setAutoCommit(true);
					Base.closeResource(connection, null, null);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取所有用户的借阅历史
	 * @param status 借阅状态
	 * @param limit 每页显示的记录数
	 * @param offset 偏移量
	 * @return List<BorrowRecord> 借阅历史列表
	 */
	public List<BorrowRecord> getAllBorrowHistory(String status, int limit, int offset) {

		List<BorrowRecord> borrowRecords = new ArrayList<>();
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = Base.getConnection();
			StringBuilder sql = new StringBuilder("SELECT br.*, u.username, u.role, b.title, b.isbn ");
			sql.append("FROM borrow_record br ");
			sql.append("JOIN user u ON br.user_id = u.user_id ");
			sql.append("JOIN book b ON br.book_id = b.book_id ");

			if (status != null && !status.trim().isEmpty()) {
				sql.append("WHERE br.status = ? ");
			}

			sql.append("ORDER BY br.borrow_date DESC ");
			sql.append("LIMIT ? OFFSET ?");

			PreparedStatement pstmt = connection.prepareStatement(sql.toString());
			int paramIndex = 1;

			if (status != null && !status.trim().isEmpty()) {
				pstmt.setString(paramIndex++, status);
			}

			pstmt.setInt(paramIndex++, limit);
			pstmt.setInt(paramIndex, offset);

			resultSet = pstmt.executeQuery();

			return getBorrowRecordByResultSet(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				Base.closeResource(connection, null, resultSet);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return borrowRecords;
	}



	// 查询借书历史
	public List<BorrowRecord> getBorrowHistory(int userId, boolean returned) {
		List<BorrowRecord> borrowRecords = new ArrayList<>();
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = Base.getConnection();
			String status = returned ? "returned" : "borrowed";
			String sql = "SELECT br.*, u.username, u.role, b.title, b.isbn " +
					"FROM borrow_record br " +
					"JOIN user u ON br.user_id = u.user_id " +
					"JOIN book b ON br.book_id = b.book_id " +
					"WHERE br.user_id = ? AND br.status = ? " +
					"ORDER BY br.borrow_date DESC";

			resultSet = Base.executeQuery(connection, sql, new Object[]{userId, status});

			return getBorrowRecordByResultSet(resultSet);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				Base.closeResource(connection, null, resultSet);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return borrowRecords;
	}


	// 还书
	public int returnBook(int userId, int bookId) {
		Connection connection = null;
		try {
			connection = Base.getConnection();
			connection.setAutoCommit(false);

			// 判断用户是否借了这本书且未还
			String checkBorrowSql = "SELECT * FROM borrow_record WHERE user_id = ? AND book_id = ? AND status = 'borrowed'";
			ResultSet resultSet = Base.executeQuery(connection, checkBorrowSql, new Object[]{userId, bookId});
			if (!resultSet.next()) {
				return 201; // 用户没有借这本书
			}

			// 更新借书记录状态为已还
			String updateRecordSql = "UPDATE borrow_record SET status = 'returned', return_date = ? WHERE user_id = ? AND book_id = ?";
			Base.executeUpdate(connection, updateRecordSql, new Object[]{new java.sql.Date(new Date().getTime()), userId, bookId});

			// 更新图书库存
			String updateBookSql = "UPDATE book SET stock_quantity = stock_quantity + 1 WHERE book_id = ?";
			Base.executeUpdate(connection, updateBookSql, new Object[]{bookId});

			connection.commit();
			return 200; // 还书成功

		} catch (SQLException | ClassNotFoundException e) {
			if (connection != null) {
				try {
					connection.rollback();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
			e.printStackTrace();
			return 500; // 系统错误
		} finally {
			try {
				if (connection != null) {
					connection.setAutoCommit(true);
					Base.closeResource(connection, null, null);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 获取借出最多的八本书
	public List<Book> getTopBorrowedBooks() {
		List<Book> books = new ArrayList<>();
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = Base.getConnection();
			String sql = "SELECT book.*, COUNT(borrow_record.book_id) AS borrow_count " +
					"FROM book " +
					"JOIN borrow_record ON book.book_id = borrow_record.book_id " +
					"GROUP BY borrow_record.book_id " +
					"ORDER BY borrow_count DESC " +
					"LIMIT 8";
			resultSet = Base.executeQuery(connection, sql, null);

			while (resultSet.next()) {
				Book book = new Book();
				book.setBookId(resultSet.getInt("book_id"));
				book.setTitle(resultSet.getString("title"));
				book.setAuthor(resultSet.getString("author"));
				book.setIsbn(resultSet.getString("isbn"));
				book.setPublisher(resultSet.getString("publisher"));
				book.setPublishDate(resultSet.getDate("publish_date"));
				book.setStockQuantity(resultSet.getInt("stock_quantity"));
				book.setType(resultSet.getString("type"));
				book.setIntroduce(resultSet.getString("introduce"));
				books.add(book);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				Base.closeResource(connection, null, resultSet);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return books;
	}





	/**
	 * 根据ResultSet 将result转变为list 借阅历史列表
	 * @param resultSet 响应json
	 * @return List<BorrowRecord> 借阅历史列表
	 * @throws SQLException
	 */
	public List<BorrowRecord> getBorrowRecordByResultSet(ResultSet resultSet) throws SQLException {
		List<BorrowRecord> borrowRecords = new ArrayList<>();
		while (resultSet.next()) {
			BorrowRecord borrowRecord = new BorrowRecord();
			borrowRecord.setRecordId(resultSet.getInt("record_id"));
			borrowRecord.setUserId(resultSet.getInt("user_id"));
			borrowRecord.setBookId(resultSet.getInt("book_id"));
			borrowRecord.setBorrowDate(resultSet.getDate("borrow_date"));
			borrowRecord.setReturnDate(resultSet.getDate("return_date"));
			borrowRecord.setStatus(resultSet.getString("status"));

			User user = new User();
			user.setUserId(resultSet.getInt("user_id"));
			user.setUsername(resultSet.getString("username"));
			user.setRole(resultSet.getString("role"));
			borrowRecord.setUser(user);

			Book book = new Book();
			book.setBookId(resultSet.getInt("book_id"));
			book.setTitle(resultSet.getString("title"));
			book.setIsbn(resultSet.getString("isbn"));
			borrowRecord.setBook(book);


			borrowRecords.add(borrowRecord);
		}

		return borrowRecords;
	}


	/**
	 * 将借阅历史列表转换为json数组
	 * @param borrowHistory 借阅历史列表
	 * @return  JSONArray 借阅历史列表的json数组
	 */
	public static JSONArray  getJsonArray(List<BorrowRecord> borrowHistory) {
		JSONArray jsonArray = new JSONArray();
		for (BorrowRecord record : borrowHistory) {
			JSONObject jsonData = new JSONObject();
			jsonData.put("recordId", record.getRecordId());
			jsonData.put("userId", record.getUserId());
			jsonData.put("bookId", record.getBookId());
			jsonData.put("borrowDate", record.getBorrowDate().toString());
			jsonData.put("status", record.getStatus());


			JSONObject userData = new JSONObject();
			userData.put("userId", record.getUser().getUserId());
			userData.put("username", record.getUser().getUsername());
			userData.put("role", record.getUser().getRole());
			jsonData.put("user", userData);

			JSONObject bookData = new JSONObject();
			bookData.put("bookId", record.getBook().getBookId());
			bookData.put("title", record.getBook().getTitle());
			bookData.put("isbn", record.getBook().getIsbn());
			jsonData.put("book", bookData);

			jsonArray.add(jsonData);
		}
		return jsonArray;
	}


}
